{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "796ed728",
   "metadata": {},
   "source": [
    "[![Kaggle](https://kaggle.com/static/images/open-in-kaggle.svg)](https://kaggle.com/kernels/welcome?src=https://github.com/pixeltable/pixeltable/blob/master/docs/release/howto/working-with-external-files.ipynb)&nbsp;&nbsp;\n",
    "[![Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/pixeltable/pixeltable/blob/master/docs/release/howto/working-with-external-files.ipynb)\n",
    "\n",
    "# Working with External Files\n",
    "\n",
    "In Pixeltable, all media data (videos, images, audio) resides in external files, and Pixeltable stores references to those. The files can be local or remote (e.g., in S3). For the latter, Pixeltable automatically caches the files locally on access.\n",
    "\n",
    "When interacting with media data via Pixeltable, either through queries or UDFs, the user sees the following Python types:\n",
    "* `ImageType`: `PIL.Image.Image`\n",
    "* `VideoType`: `string` (local path)\n",
    "* `AudioType`: `string` (local path)\n",
    "\n",
    "Let's create a table and load some data to see what that looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f92af855",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:51.125027Z",
     "iopub.status.busy": "2024-05-25T22:10:51.124481Z",
     "iopub.status.idle": "2024-05-25T22:10:53.520343Z",
     "shell.execute_reply": "2024-05-25T22:10:53.520007Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to Pixeltable database at: postgresql://postgres:@/pixeltable?host=/Users/orm/.pixeltable/pgdata\n"
     ]
    }
   ],
   "source": [
    "import tempfile\n",
    "import random\n",
    "import shutil\n",
    "import pixeltable as pxt\n",
    "\n",
    "pxt.drop_dir('external_data', force=True)  # Ensure a clean slate for the tutorial\n",
    "pxt.create_dir('external_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3833c00a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:53.522085Z",
     "iopub.status.busy": "2024-05-25T22:10:53.521963Z",
     "iopub.status.idle": "2024-05-25T22:10:58.047489Z",
     "shell.execute_reply": "2024-05-25T22:10:58.047196Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created table `videos`.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 0 rows [00:00, ? rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 3 rows [00:00, 897.69 rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Inserted 3 rows with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=3, num_computed_values=0, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pxt.drop_table('external_data.videos', ignore_errors=True)\n",
    "v = pxt.create_table('external_data.videos', {'video': pxt.VideoType()})\n",
    "\n",
    "prefix = 's3://multimedia-commons/'\n",
    "paths = [\n",
    "    'data/videos/mp4/ffe/ffb/ffeffbef41bbc269810b2a1a888de.mp4',\n",
    "    'data/videos/mp4/ffe/feb/ffefebb41485539f964760e6115fbc44.mp4',\n",
    "    'data/videos/mp4/ffe/f73/ffef7384d698b5f70d411c696247169.mp4'\n",
    "]\n",
    "v.insert({'video': prefix + p} for p in paths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cbc0e0b",
   "metadata": {},
   "source": [
    "We just inserted 3 rows with video files residing in S3. When we now query these, we are presented with their locally cached counterparts.\n",
    "\n",
    "(Note: we don't simply display the output of `collect()` here, because that is formatted as an HTML table with a media player and so would obscure the file path.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b01b904b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.048950Z",
     "iopub.status.busy": "2024-05-25T22:10:58.048847Z",
     "iopub.status.idle": "2024-05-25T22:10:58.053583Z",
     "shell.execute_reply": "2024-05-25T22:10:58.053361Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'video': '/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_1fcfcb221263cff76a2853250fbbb2e90375dd495454c0007bc6ff4430c9a4a7.mp4'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = list(v.select(v.video).collect())\n",
    "rows[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93627594",
   "metadata": {},
   "source": [
    "Let's make a local copy of the first file and insert that separately. First, the copy:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4c9e4016",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.054901Z",
     "iopub.status.busy": "2024-05-25T22:10:58.054808Z",
     "iopub.status.idle": "2024-05-25T22:10:58.058398Z",
     "shell.execute_reply": "2024-05-25T22:10:58.058141Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpmeecpn92.mp4'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "local_path = tempfile.mktemp(suffix='.mp4')\n",
    "shutil.copyfile(rows[0]['video'], local_path)\n",
    "local_path"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b432eb4",
   "metadata": {},
   "source": [
    "Now the insert:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e1d3f0bd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.059737Z",
     "iopub.status.busy": "2024-05-25T22:10:58.059631Z",
     "iopub.status.idle": "2024-05-25T22:10:58.081412Z",
     "shell.execute_reply": "2024-05-25T22:10:58.081167Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 0 rows [00:00, ? rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 1 rows [00:00, 1396.70 rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Inserted 1 row with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=1, num_computed_values=0, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.insert(video=local_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc1915be",
   "metadata": {},
   "source": [
    "When we query this again, we see that local paths are preserved:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b6a7da46",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.082838Z",
     "iopub.status.busy": "2024-05-25T22:10:58.082726Z",
     "iopub.status.idle": "2024-05-25T22:10:58.086629Z",
     "shell.execute_reply": "2024-05-25T22:10:58.086416Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'video': '/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_1fcfcb221263cff76a2853250fbbb2e90375dd495454c0007bc6ff4430c9a4a7.mp4'},\n",
       " {'video': '/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_fc11428b32768ae782193a57ebcbad706f45bbd9fa13354471e0bcd798fee3ea.mp4'},\n",
       " {'video': '/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_b9fb0d9411bc9cd183a36866911baa7a8834f22f665bce47608566b38485c16a.mp4'},\n",
       " {'video': '/var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpmeecpn92.mp4'}]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = list(v.select(v.video).collect())\n",
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe7f594",
   "metadata": {},
   "source": [
    "UDFs also see local paths:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "aa960408",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.087948Z",
     "iopub.status.busy": "2024-05-25T22:10:58.087848Z",
     "iopub.status.idle": "2024-05-25T22:10:58.089784Z",
     "shell.execute_reply": "2024-05-25T22:10:58.089568Z"
    }
   },
   "outputs": [],
   "source": [
    "@pxt.udf(param_types=[pxt.VideoType()])\n",
    "def f(v: str) -> int:\n",
    "    print(f'{type(v)}: {v}')\n",
    "    return 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "12f64b10",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.091001Z",
     "iopub.status.busy": "2024-05-25T22:10:58.090901Z",
     "iopub.status.idle": "2024-05-25T22:10:58.098284Z",
     "shell.execute_reply": "2024-05-25T22:10:58.098045Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'str'>: /Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_1fcfcb221263cff76a2853250fbbb2e90375dd495454c0007bc6ff4430c9a4a7.mp4\n",
      "<class 'str'>: /Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_fc11428b32768ae782193a57ebcbad706f45bbd9fa13354471e0bcd798fee3ea.mp4\n",
      "<class 'str'>: /Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_b9fb0d9411bc9cd183a36866911baa7a8834f22f665bce47608566b38485c16a.mp4\n",
      "<class 'str'>: /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpmeecpn92.mp4\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>col_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   col_0\n",
       "0      1\n",
       "1      1\n",
       "2      1\n",
       "3      1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.select(f(v.video)).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc19779e",
   "metadata": {},
   "source": [
    "## Dealing with errors\n",
    "\n",
    "When interacting with media data in Pixeltable, the user can assume that the underlying files exist, are local and are valid for their respective data type. In other words, the user doesn't need to consider error conditions.\n",
    "\n",
    "To that end, Pixeltable validates media data on ingest. The default behavior is to reject invalid media files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "db134d55",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:10:58.099642Z",
     "iopub.status.busy": "2024-05-25T22:10:58.099529Z",
     "iopub.status.idle": "2024-05-25T22:11:01.133105Z",
     "shell.execute_reply": "2024-05-25T22:11:01.132798Z"
    },
    "scrolled": true,
    "tags": [
     "raises-exception"
    ]
   },
   "outputs": [
    {
     "ename": "Error",
     "evalue": "Failed to download s3://multimedia-commons/bad_path.mp4: An error occurred (404) when calling the HeadObject operation: Not Found",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mError\u001b[0m                                     Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[9], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mv\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mvideo\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mprefix\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m+\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mbad_path.mp4\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/insertable_table.py:134\u001b[0m, in \u001b[0;36mInsertableTable.insert\u001b[0;34m(self, rows, print_stats, fail_on_exception, **kwargs)\u001b[0m\n\u001b[1;32m    132\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m excs\u001b[38;5;241m.\u001b[39mError(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mrows must be a list of dictionaries\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m    133\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_input_rows(rows)\n\u001b[0;32m--> 134\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mtbl_version\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mrows\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mprint_stats\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mprint_stats\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfail_on_exception\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfail_on_exception\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    136\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m result\u001b[38;5;241m.\u001b[39mnum_excs \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m0\u001b[39m:\n\u001b[1;32m    137\u001b[0m     cols_with_excs_str \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m'\u001b[39m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/table_version.py:600\u001b[0m, in \u001b[0;36mTableVersion.insert\u001b[0;34m(self, rows, print_stats, fail_on_exception)\u001b[0m\n\u001b[1;32m    598\u001b[0m ts \u001b[38;5;241m=\u001b[39m time\u001b[38;5;241m.\u001b[39mtime()\n\u001b[1;32m    599\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m Env\u001b[38;5;241m.\u001b[39mget()\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39mbegin() \u001b[38;5;28;01mas\u001b[39;00m conn:\n\u001b[0;32m--> 600\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_insert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mplan\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mts\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mprint_stats\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/table_version.py:609\u001b[0m, in \u001b[0;36mTableVersion._insert\u001b[0;34m(self, exec_plan, conn, ts, print_stats)\u001b[0m\n\u001b[1;32m    607\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mversion \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1\u001b[39m\n\u001b[1;32m    608\u001b[0m result \u001b[38;5;241m=\u001b[39m UpdateStatus()\n\u001b[0;32m--> 609\u001b[0m num_rows, num_excs, cols_with_excs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstore_tbl\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert_rows\u001b[49m\u001b[43m(\u001b[49m\u001b[43mexec_plan\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mv_min\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mversion\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    610\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mnext_rowid \u001b[38;5;241m=\u001b[39m num_rows\n\u001b[1;32m    611\u001b[0m result\u001b[38;5;241m.\u001b[39mnum_rows \u001b[38;5;241m=\u001b[39m num_rows\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/store.py:285\u001b[0m, in \u001b[0;36mStoreBase.insert_rows\u001b[0;34m(self, exec_plan, conn, v_min)\u001b[0m\n\u001b[1;32m    283\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m    284\u001b[0m     exec_plan\u001b[38;5;241m.\u001b[39mopen()\n\u001b[0;32m--> 285\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m row_batch \u001b[38;5;129;01min\u001b[39;00m exec_plan:\n\u001b[1;32m    286\u001b[0m         num_rows \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlen\u001b[39m(row_batch)\n\u001b[1;32m    287\u001b[0m         \u001b[38;5;28;01mfor\u001b[39;00m batch_start_idx \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mrange\u001b[39m(\u001b[38;5;241m0\u001b[39m, \u001b[38;5;28mlen\u001b[39m(row_batch), batch_size):\n\u001b[1;32m    288\u001b[0m             \u001b[38;5;66;03m# compute batch of rows and convert them into table rows\u001b[39;00m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/exec/media_validation_node.py:26\u001b[0m, in \u001b[0;36mMediaValidationNode.__next__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     24\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m__next__\u001b[39m(\u001b[38;5;28mself\u001b[39m) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m DataRowBatch:\n\u001b[1;32m     25\u001b[0m     \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39minput \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m---> 26\u001b[0m     row_batch \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mnext\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minput\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     27\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m row \u001b[38;5;129;01min\u001b[39;00m row_batch:\n\u001b[1;32m     28\u001b[0m         \u001b[38;5;28;01mfor\u001b[39;00m slot_idx, col \u001b[38;5;129;01min\u001b[39;00m [(c\u001b[38;5;241m.\u001b[39mslot_idx, c\u001b[38;5;241m.\u001b[39mcol) \u001b[38;5;28;01mfor\u001b[39;00m c \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmedia_slots]:\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/exec/cache_prefetch_node.py:70\u001b[0m, in \u001b[0;36mCachePrefetchNode.__next__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     67\u001b[0m     futures[executor\u001b[38;5;241m.\u001b[39msubmit(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_fetch_url, row, info\u001b[38;5;241m.\u001b[39mslot_idx)] \u001b[38;5;241m=\u001b[39m (row, info)\n\u001b[1;32m     68\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m future \u001b[38;5;129;01min\u001b[39;00m concurrent\u001b[38;5;241m.\u001b[39mfutures\u001b[38;5;241m.\u001b[39mas_completed(futures):\n\u001b[1;32m     69\u001b[0m     \u001b[38;5;66;03m# TODO:  does this need to deal with recoverable errors (such as retry after throttling)?\u001b[39;00m\n\u001b[0;32m---> 70\u001b[0m     tmp_path \u001b[38;5;241m=\u001b[39m \u001b[43mfuture\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mresult\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     71\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m tmp_path \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m     72\u001b[0m         \u001b[38;5;28;01mcontinue\u001b[39;00m\n",
      "File \u001b[0;32m~/mambaforge/envs/pixeltable_39/lib/python3.9/concurrent/futures/_base.py:439\u001b[0m, in \u001b[0;36mFuture.result\u001b[0;34m(self, timeout)\u001b[0m\n\u001b[1;32m    437\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m CancelledError()\n\u001b[1;32m    438\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_state \u001b[38;5;241m==\u001b[39m FINISHED:\n\u001b[0;32m--> 439\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m__get_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    441\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_condition\u001b[38;5;241m.\u001b[39mwait(timeout)\n\u001b[1;32m    443\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_state \u001b[38;5;129;01min\u001b[39;00m [CANCELLED, CANCELLED_AND_NOTIFIED]:\n",
      "File \u001b[0;32m~/mambaforge/envs/pixeltable_39/lib/python3.9/concurrent/futures/_base.py:391\u001b[0m, in \u001b[0;36mFuture.__get_result\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    389\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_exception:\n\u001b[1;32m    390\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 391\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_exception\n\u001b[1;32m    392\u001b[0m     \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m    393\u001b[0m         \u001b[38;5;66;03m# Break a reference cycle with the exception in self._exception\u001b[39;00m\n\u001b[1;32m    394\u001b[0m         \u001b[38;5;28mself\u001b[39m \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "File \u001b[0;32m~/mambaforge/envs/pixeltable_39/lib/python3.9/concurrent/futures/thread.py:58\u001b[0m, in \u001b[0;36m_WorkItem.run\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     55\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m\n\u001b[1;32m     57\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m---> 58\u001b[0m     result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfn\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     59\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m exc:\n\u001b[1;32m     60\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mfuture\u001b[38;5;241m.\u001b[39mset_exception(exc)\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/exec/cache_prefetch_node.py:114\u001b[0m, in \u001b[0;36mCachePrefetchNode._fetch_url\u001b[0;34m(self, row, slot_idx)\u001b[0m\n\u001b[1;32m    112\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mrow_builder\u001b[38;5;241m.\u001b[39mset_exc(row, slot_idx, exc)\n\u001b[1;32m    113\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mctx\u001b[38;5;241m.\u001b[39mignore_errors:\n\u001b[0;32m--> 114\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m exc \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m  \u001b[38;5;66;03m# suppress original exception\u001b[39;00m\n\u001b[1;32m    115\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "\u001b[0;31mError\u001b[0m: Failed to download s3://multimedia-commons/bad_path.mp4: An error occurred (404) when calling the HeadObject operation: Not Found"
     ]
    }
   ],
   "source": [
    "v.insert(video=prefix + 'bad_path.mp4')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f46d7ff",
   "metadata": {},
   "source": [
    "The same happens for corrupted files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ceb71c70",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:11:01.134749Z",
     "iopub.status.busy": "2024-05-25T22:11:01.134618Z",
     "iopub.status.idle": "2024-05-25T22:11:01.199160Z",
     "shell.execute_reply": "2024-05-25T22:11:01.198833Z"
    },
    "scrolled": true,
    "tags": [
     "raises-exception"
    ]
   },
   "outputs": [
    {
     "ename": "Error",
     "evalue": "Not a valid video: /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpis_08o50.mp4",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mError\u001b[0m                                     Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[10], line 6\u001b[0m\n\u001b[1;32m      3\u001b[0m     temp_file\u001b[38;5;241m.\u001b[39mwrite(random\u001b[38;5;241m.\u001b[39mrandbytes(\u001b[38;5;241m1024\u001b[39m))\n\u001b[1;32m      4\u001b[0m     corrupted_path \u001b[38;5;241m=\u001b[39m temp_file\u001b[38;5;241m.\u001b[39mname\n\u001b[0;32m----> 6\u001b[0m \u001b[43mv\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mvideo\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcorrupted_path\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/insertable_table.py:134\u001b[0m, in \u001b[0;36mInsertableTable.insert\u001b[0;34m(self, rows, print_stats, fail_on_exception, **kwargs)\u001b[0m\n\u001b[1;32m    132\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m excs\u001b[38;5;241m.\u001b[39mError(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mrows must be a list of dictionaries\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m    133\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_input_rows(rows)\n\u001b[0;32m--> 134\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mtbl_version\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mrows\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mprint_stats\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mprint_stats\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfail_on_exception\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfail_on_exception\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    136\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m result\u001b[38;5;241m.\u001b[39mnum_excs \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m0\u001b[39m:\n\u001b[1;32m    137\u001b[0m     cols_with_excs_str \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m'\u001b[39m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/table_version.py:600\u001b[0m, in \u001b[0;36mTableVersion.insert\u001b[0;34m(self, rows, print_stats, fail_on_exception)\u001b[0m\n\u001b[1;32m    598\u001b[0m ts \u001b[38;5;241m=\u001b[39m time\u001b[38;5;241m.\u001b[39mtime()\n\u001b[1;32m    599\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m Env\u001b[38;5;241m.\u001b[39mget()\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39mbegin() \u001b[38;5;28;01mas\u001b[39;00m conn:\n\u001b[0;32m--> 600\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_insert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mplan\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mts\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mprint_stats\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/catalog/table_version.py:609\u001b[0m, in \u001b[0;36mTableVersion._insert\u001b[0;34m(self, exec_plan, conn, ts, print_stats)\u001b[0m\n\u001b[1;32m    607\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mversion \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1\u001b[39m\n\u001b[1;32m    608\u001b[0m result \u001b[38;5;241m=\u001b[39m UpdateStatus()\n\u001b[0;32m--> 609\u001b[0m num_rows, num_excs, cols_with_excs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstore_tbl\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43minsert_rows\u001b[49m\u001b[43m(\u001b[49m\u001b[43mexec_plan\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mv_min\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mversion\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    610\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mnext_rowid \u001b[38;5;241m=\u001b[39m num_rows\n\u001b[1;32m    611\u001b[0m result\u001b[38;5;241m.\u001b[39mnum_rows \u001b[38;5;241m=\u001b[39m num_rows\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/store.py:285\u001b[0m, in \u001b[0;36mStoreBase.insert_rows\u001b[0;34m(self, exec_plan, conn, v_min)\u001b[0m\n\u001b[1;32m    283\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m    284\u001b[0m     exec_plan\u001b[38;5;241m.\u001b[39mopen()\n\u001b[0;32m--> 285\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m row_batch \u001b[38;5;129;01min\u001b[39;00m exec_plan:\n\u001b[1;32m    286\u001b[0m         num_rows \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlen\u001b[39m(row_batch)\n\u001b[1;32m    287\u001b[0m         \u001b[38;5;28;01mfor\u001b[39;00m batch_start_idx \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mrange\u001b[39m(\u001b[38;5;241m0\u001b[39m, \u001b[38;5;28mlen\u001b[39m(row_batch), batch_size):\n\u001b[1;32m    288\u001b[0m             \u001b[38;5;66;03m# compute batch of rows and convert them into table rows\u001b[39;00m\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/exec/media_validation_node.py:41\u001b[0m, in \u001b[0;36mMediaValidationNode.__next__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     39\u001b[0m             \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mrow_builder\u001b[38;5;241m.\u001b[39mset_exc(row, slot_idx, exc)\n\u001b[1;32m     40\u001b[0m             \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mctx\u001b[38;5;241m.\u001b[39mignore_errors:\n\u001b[0;32m---> 41\u001b[0m                 \u001b[38;5;28;01mraise\u001b[39;00m exc\n\u001b[1;32m     43\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m row_batch\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/exec/media_validation_node.py:37\u001b[0m, in \u001b[0;36mMediaValidationNode.__next__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     34\u001b[0m     \u001b[38;5;28;01mcontinue\u001b[39;00m\n\u001b[1;32m     36\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m---> 37\u001b[0m     \u001b[43mcol\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcol_type\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mvalidate_media\u001b[49m\u001b[43m(\u001b[49m\u001b[43mpath\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     38\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m excs\u001b[38;5;241m.\u001b[39mError \u001b[38;5;28;01mas\u001b[39;00m exc:\n\u001b[1;32m     39\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mrow_builder\u001b[38;5;241m.\u001b[39mset_exc(row, slot_idx, exc)\n",
      "File \u001b[0;32m~/repos/pixeltable/pixeltable/type_system.py:783\u001b[0m, in \u001b[0;36mVideoType.validate_media\u001b[0;34m(self, val)\u001b[0m\n\u001b[1;32m    781\u001b[0m             \u001b[38;5;28;01mraise\u001b[39;00m excs\u001b[38;5;241m.\u001b[39mError(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mNot a valid video: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mval\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m    782\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m av\u001b[38;5;241m.\u001b[39mAVError:\n\u001b[0;32m--> 783\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m excs\u001b[38;5;241m.\u001b[39mError(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mNot a valid video: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mval\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "\u001b[0;31mError\u001b[0m: Not a valid video: /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpis_08o50.mp4"
     ]
    }
   ],
   "source": [
    "# create invalid .mp4\n",
    "with tempfile.NamedTemporaryFile(mode='wb', suffix='.mp4', delete=False) as temp_file:\n",
    "    temp_file.write(random.randbytes(1024))\n",
    "    corrupted_path = temp_file.name\n",
    "\n",
    "v.insert(video=corrupted_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51954ecf",
   "metadata": {},
   "source": [
    "Alternatively, Pixeltable can also be instructed to record error conditions and proceed with the ingest, via the `fail_on_exception` flag (default: `True`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "06964c4e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:11:01.200727Z",
     "iopub.status.busy": "2024-05-25T22:11:01.200605Z",
     "iopub.status.idle": "2024-05-25T22:11:01.689408Z",
     "shell.execute_reply": "2024-05-25T22:11:01.688092Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 0 rows [00:00, ? rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r",
      "Inserting rows into `videos`: 2 rows [00:00, 621.89 rows/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Inserted 2 rows with 2 errors across 1 column (videos.video).\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=2, num_computed_values=0, num_excs=2, updated_cols=[], cols_with_excs=['videos.video'])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.insert([{'video': prefix + 'bad_path.mp4'}, {'video': corrupted_path}], fail_on_exception=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cd6be2c",
   "metadata": {},
   "source": [
    "Every media column has properties `errortype` and `errormsg` (both containing `string` data) that indicate whether the column value is valid. Invalid values show up as `None` and have non-null `errortype`/`errormsg`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c96938f2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:11:01.694120Z",
     "iopub.status.busy": "2024-05-25T22:11:01.693673Z",
     "iopub.status.idle": "2024-05-25T22:11:01.706959Z",
     "shell.execute_reply": "2024-05-25T22:11:01.706550Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>col_0</th>\n",
       "      <th>video_errortype</th>\n",
       "      <th>video_errormsg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>True</td>\n",
       "      <td>Error</td>\n",
       "      <td>Failed to download s3://multimedia-commons/bad_path.mp4: An error occurred (404) when calling the HeadObject operation: Not Found</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>True</td>\n",
       "      <td>Error</td>\n",
       "      <td>Not a valid video: /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpis_08o50.mp4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   col_0 video_errortype                                     video_errormsg\n",
       "0  False            None                                               None\n",
       "1  False            None                                               None\n",
       "2  False            None                                               None\n",
       "3  False            None                                               None\n",
       "4   True           Error  Failed to download s3://multimedia-commons/bad...\n",
       "5   True           Error  Not a valid video: /var/folders/8v/d886z5j13ds..."
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.select(v.video == None, v.video.errortype, v.video.errormsg).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d349ea5",
   "metadata": {},
   "source": [
    "Errors can now be inspected (and corrected) after the ingest:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fe025ca3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:11:01.709243Z",
     "iopub.status.busy": "2024-05-25T22:11:01.709055Z",
     "iopub.status.idle": "2024-05-25T22:11:01.716409Z",
     "shell.execute_reply": "2024-05-25T22:11:01.716098Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>video_errormsg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Failed to download s3://multimedia-commons/bad_path.mp4: An error occurred (404) when calling the HeadObject operation: Not Found</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Not a valid video: /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpis_08o50.mp4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "                                      video_errormsg\n",
       "0  Failed to download s3://multimedia-commons/bad...\n",
       "1  Not a valid video: /var/folders/8v/d886z5j13ds..."
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.where(v.video.errortype != None).select(v.video.errormsg).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b55d67b",
   "metadata": {},
   "source": [
    "## Accessing the original file paths\n",
    "\n",
    "In some cases, it will be necessary to access file paths (not, say, the `PIL.Image.Image`), and Pixeltable provides the column properties `fileurl` and `localpath` for that purpose:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a606c708",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-05-25T22:11:01.718487Z",
     "iopub.status.busy": "2024-05-25T22:11:01.718319Z",
     "iopub.status.idle": "2024-05-25T22:11:01.725287Z",
     "shell.execute_reply": "2024-05-25T22:11:01.724985Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>video_fileurl</th>\n",
       "      <th>video_localpath</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>s3://multimedia-commons/data/videos/mp4/ffe/ffb/ffeffbef41bbc269810b2a1a888de.mp4</td>\n",
       "      <td>/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_1fcfcb221263cff76a2853250fbbb2e90375dd495454c0007bc6ff4430c9a4a7.mp4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>s3://multimedia-commons/data/videos/mp4/ffe/feb/ffefebb41485539f964760e6115fbc44.mp4</td>\n",
       "      <td>/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_fc11428b32768ae782193a57ebcbad706f45bbd9fa13354471e0bcd798fee3ea.mp4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>s3://multimedia-commons/data/videos/mp4/ffe/f73/ffef7384d698b5f70d411c696247169.mp4</td>\n",
       "      <td>/Users/orm/.pixeltable/file_cache/970801cdb9664e309e1bf50429103896_0_b9fb0d9411bc9cd183a36866911baa7a8834f22f665bce47608566b38485c16a.mp4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>file:///var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpmeecpn92.mp4</td>\n",
       "      <td>/var/folders/8v/d886z5j13dsctyjpw29t7y480000gn/T/tmpmeecpn92.mp4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "                                       video_fileurl  \\\n",
       "0  s3://multimedia-commons/data/videos/mp4/ffe/ff...   \n",
       "1  s3://multimedia-commons/data/videos/mp4/ffe/fe...   \n",
       "2  s3://multimedia-commons/data/videos/mp4/ffe/f7...   \n",
       "3  file:///var/folders/8v/d886z5j13dsctyjpw29t7y4...   \n",
       "4                                               None   \n",
       "5                                               None   \n",
       "\n",
       "                                     video_localpath  \n",
       "0  /Users/orm/.pixeltable/file_cache/970801cdb966...  \n",
       "1  /Users/orm/.pixeltable/file_cache/970801cdb966...  \n",
       "2  /Users/orm/.pixeltable/file_cache/970801cdb966...  \n",
       "3  /var/folders/8v/d886z5j13dsctyjpw29t7y480000gn...  \n",
       "4                                               None  \n",
       "5                                               None  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.select(v.video.fileurl, v.video.localpath).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af1b02a7",
   "metadata": {},
   "source": [
    "Note that for local media files, the `fileurl` property still returns a parsable URL."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
